package cn.fuxi2022.module.excel.mapper;

import cn.fuxi2022.module.excel.pojo.DemoData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ExcelUseDataMapper {

    /**
     * 查询学生的所有信息
     */
    @Select("SELECT\n" +
            "\tgroup_student.`id` AS `studentId`,\n" +
            "\treal_name AS `name`,\n" +
            "\t`mobile` AS `phone`,\n" +
            "\t`student_number` AS `studentSchoolId`,\n" +
            "\tgroup_student_info.`sex`,\n" +
            "\t`college` AS `collegeName`,\n" +
            "\t`class` AS `className`,\n" +
            "\t`dorm`,\n" +
            "\t`degree` AS `level`,\n" +
            "\t`direction`,\n" +
            "\t`is_student` `status`,\n" +
            "\tgroup_student.gmt_create AS time,\n" +
            "\tgroup_student_info.gmt_modified AS successfulTime,\n" +
            "\tUNIX_TIMESTAMP( group_student_info.gmt_modified ) - UNIX_TIMESTAMP( group_student.gmt_create ) AS `useTime` \n" +
            "FROM\n" +
            "\tgroup_student\n" +
            "\tLEFT JOIN group_student_info ON group_student_info.student_id = group_student.id \n" +
            "ORDER BY\n" +
            "\tgroup_student.gmt_create")
    List<DemoData> selectInfo();

    /**
     * 若当前社员支付成功，查询他最后的订单流水号
     *
     * @param studentId 支付成功的社员
     */
    @Select("SELECT\n" +
            "\t`order_no` AS order_no,\n" +
            "\tUNIX_TIMESTAMP(pay_time) - UNIX_TIMESTAMP(group_student_pay_log.gmt_create) AS orderTime\n" +
            "FROM\n" +
            "\tgroup_student_pay_log \n" +
            "WHERE\n" +
            "\tstudent_id = #{studentId} \n" +
            "ORDER BY\n" +
            "\tgmt_create DESC\n" +
            "LIMIT 1")
    DemoData selectPay(String studentId);

    /**
     * 查询订单个数
     */
    @Select("SELECT\n" +
            "\tcount(id) AS orderCount\n" +
            "FROM\n" +
            "\tgroup_student_pay_log \n" +
            "WHERE\n" +
            "\tstudent_id = #{studentId} ")
    Integer selectPayOrderCount(String studentId);

    @Select("SELECT COUNT(id) FROM group_register WHERE student_id = #{studentId}")
    Integer selectRegisterCount(String studentId);

}
